﻿using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Threading;
using System.Xml.Linq;
using UnitTesting = Microsoft.VisualStudio.TestTools.UnitTesting;

namespace DBAccessTestFramework
{
    public class DBTestSupport
    {
        private List<KeyValuePair<string, XElement>> inputs = new List<KeyValuePair<string, XElement>>();
        private string connectionString;
        private IDatabaseClient databaseClient;
        private static object synchObj = new object();

        public TestContext TestContext { get; set; }

        public DBTestSupport AddInput(string filename)
        {
            var xml = XElement.Load(filename);
            var input = new KeyValuePair<string, XElement>(filename, xml);
            this.inputs.Add(input);
            return this;
        }

        public void SetConnectionString(string connectionStringName)
        {
            var connectionStringDetail = ConfigurationManager.ConnectionStrings[connectionStringName];
            if (connectionStringDetail == null)
            {
                string message = string.Format("Connection String '{0}' not found", connectionStringName);
                throw new Exception(message);
            }

            this.connectionString = connectionStringDetail.ConnectionString;
        }

        public void SetDatabaseClient(IDatabaseClient databaseClient)
        {
            this.databaseClient = databaseClient;
        }

        public void LoadTable(string datasetName)
        {
            this.ValidateDependencies();
            DatasetElement dataset = this.FindDataset(datasetName);
            this.databaseClient.WriteTable(this.connectionString, dataset);
        }

        public void ExecuteScript(string scriptName)
        {
            this.ValidateDependencies();
            ScriptElement scriptElement = this.FindScript(scriptName);
            this.databaseClient.ExecuteCommand(this.connectionString, scriptElement.Value);
        }

        public void AssertDataUsingInputFile(
            string keysCsv,
            [CallerMemberName]string expectedDatasetName = "",
            [CallerMemberName]string actualScriptName = "",
            bool sorted = false)
        {
            this.ValidateDependencies();
            string[] keys = keysCsv.Split(',').Select(a => a.Trim()).ToArray();

            DatasetElement expectedData = this.FindDataset(expectedDatasetName);

            ScriptElement actualDataScript = this.FindScript(actualScriptName);
            DatasetElement actualData = this.databaseClient.ReadTable(this.connectionString, actualDataScript.Value);

            this.CompareDatasets(expectedData, actualData, keys, sorted, false);
        }

        public void AssertDataUsingCommand(string keysCsv, string expectedXmlData, string actualSQLScript, bool sorted = false)
        {
            this.ValidateDependencies(true);
            string[] keys = keysCsv.Split(',').Select(a => a.Trim()).ToArray();

            DatasetElement expectedData = new DatasetElement { Data = XElement.Parse(expectedXmlData) };

            ScriptElement actualDataScript = new ScriptElement { Source = ScriptElement.ScriptType.Command, Value = actualSQLScript };
            DatasetElement actualData = this.databaseClient.ReadTable(this.connectionString, actualDataScript.Value);
            this.CompareDatasets(expectedData, actualData, keys, sorted, false);
        }

        public void Lock()
        {
            Monitor.Enter(synchObj);
        }

        public void Unlock()
        {
            Monitor.Exit(synchObj);
        }

        private void CompareDatasets(DatasetElement expected, DatasetElement actual, string[] keys, bool sorted, bool compareTableName = true)
        {
            if (compareTableName)
            {
                UnitTesting.Assert.AreEqual(expected.Table, actual.Table, "Table names are different.");
            }

            UnitTesting.Assert.AreEqual(expected.Data.Elements().Count(), actual.Data.Elements().Count(), "The number of rows are different.");
            if (sorted)
            {
                this.CompareSortedElements(expected.Data, actual.Data);
            }
            else
            {
                this.CompareUnsortedElements(expected.Data, actual.Data, keys);
            }
        }

        private void CompareUnsortedElements(XElement expected, XElement actual, string[] keys)
        {
            foreach (var expectedElement in expected.Elements())
            {
                List<KeyValuePair<string, string>> keysAndValues = new List<KeyValuePair<string, string>>();
                foreach (var key in keys)
                {
                    string value = string.Empty;
                    var attribute = expectedElement.Attribute(key);

                    if (attribute != null)
                    {
                        value = attribute.Value;
                    }

                    var item = new KeyValuePair<string, string>(key, value);
                    keysAndValues.Add(item);
                }

                XElement actualElement = this.FindElementByKeyAndValues(keysAndValues, actual);
                this.CompareElements(expectedElement, actualElement);
            }
        }

        private void CompareElements(XElement expected, XElement actual)
        {
            foreach (var expectedAttribute in expected.Attributes())
            {
                var actualAttribute = actual.Attribute(expectedAttribute.Name);
                UnitTesting.Assert.IsNotNull(actualAttribute, "Attribute '{0}' not found in actual data.", expectedAttribute.Name);
                UnitTesting.Assert.AreEqual(expectedAttribute.Value, actualAttribute.Value, "Values for field '{0}' are different.", expectedAttribute.Name);
            }
        }

        private XElement FindElementByKeyAndValues(List<KeyValuePair<string, string>> keysAndValues, XElement xml)
        {
            // validate that all the key attributes are found.
            foreach (var element in xml.Elements())
            {
                foreach (var keyAndValue in keysAndValues)
                {
                    var attribute = element.Attribute(keyAndValue.Key);
                    if (attribute == null)
                    {
                        string message = string.Format("Key attribute '{0}' not found. Element: {1}", keyAndValue.Key, element.ToString());
                        throw new InvalidOperationException(message);
                    }
                }
            }

            var query = xml.Elements()
                .Where(a => a.Attribute(keysAndValues[0].Key).Value == keysAndValues[0].Value)
                .Select(a => a);

            for (int i = 1; i < keysAndValues.Count; i++)
            {
                int local = i;
                query = query
                    .Where(a => a.Attribute(keysAndValues[local].Key).Value == keysAndValues[local].Value)
                    .Select(a => a);
            }

            var result = query.ToArray();
            UnitTesting.Assert.IsFalse(result.Length == 0, "Row not found. {0}", this.KeysAndValuesToString(keysAndValues));
            UnitTesting.Assert.IsFalse(result.Length > 1, "More than one row found. {0}", this.KeysAndValuesToString(keysAndValues));

            return result[0];
        }

        private string KeysAndValuesToString(List<KeyValuePair<string, string>> keysAndValues)
        {
            var list = keysAndValues.Select(a => string.Format("Key: {0}, Value: {1}", a.Key, a.Value));
            return string.Join(";", list);
        }

        private void CompareSortedElements(XElement expected, XElement actual)
        {
            var expectedArray = expected.Elements()
                .ToArray();

            var actualArray = actual.Elements()
                .ToArray();

            for (int i = 0; i < expectedArray.Length; i++)
            {
                var expectedElement = expectedArray[i];
                var actualElement = actualArray[i];
                this.CompareElements(expectedElement, actualElement);
            }
        }

        private ScriptElement FindScript(string scriptName)
        {
            foreach (var input in this.inputs)
            {
                var scriptElement = this.FindScript(scriptName, input.Key, input.Value);
                if (scriptElement != null)
                {
                    return scriptElement;
                }
            }

            string message = string.Format("Script '{0}' not found", scriptName);
            throw new InvalidOperationException(message);
        }

        private ScriptElement FindScript(string scriptName, string filename, XElement xml)
        {
            var script = this.FindChild(xml, FileDefinitionHelper.ElementNameSripts, scriptName);
            if (script != null)
            {
                return ScriptElement.Load(script, filename);
            }

            return null;
        }

        private DatasetElement FindDataset(string datasetName)
        {
            foreach (var input in this.inputs)
            {
                var datasetElement = this.FindDataset(datasetName, input.Value);
                if (datasetElement != null)
                {
                    return datasetElement;
                }
            }

            string message = string.Format("Dataset '{0}' not found", datasetName);
            throw new InvalidOperationException(message);
        }

        private DatasetElement FindDataset(string datasetName, XElement xml)
        {
            var dataset = this.FindChild(xml, FileDefinitionHelper.ElementNameDatasets, datasetName);
            if (dataset != null)
            {
                return DatasetElement.Load(dataset);
            }

            return null;
        }

        private void ValidateDependencies(bool allowTestWithoutInputFile = false)
        {
            if (this.databaseClient == null)
            {
                this.databaseClient = new SQLServerDatabaseClient();
            }

            if (string.IsNullOrWhiteSpace(this.connectionString))
            {
                throw new InvalidOperationException("ConnectionString not specified");
            }

            if (!allowTestWithoutInputFile && this.inputs.Count == 0)
            {
                throw new InvalidOperationException("Input file not specified");
            }
        }

        private XElement FindChild(XElement xml, string parentName, string childName)
        {
            XElement parentElement = xml.Elements()
                .FirstOrDefault(a => a.Name.LocalName == parentName);

            if (parentElement != null)
            {
                foreach (var child in parentElement.Elements())
                {
                    var attribute = child.Attribute("name");
                    if (attribute == null)
                    {
                        continue;
                    }

                    if (attribute.Value == childName)
                    {
                        return child;
                    }
                }
            }

            return null;
        }
    }
}
